<?php
/**
* This file acts to keep the db scheme updated.  It should be run on the mysql backend db after code has been
*     updated.  It is safe to run on existing data, as it will look at the current db storage scheme & update it
*    without removing existing data.
* You have to be logged in as news to run this file.  Users trying to access it without having that login will
*    be presented with an error.
*
* @package folio
**/

// The current version of the folio db scheme.
$folio_dbversion = 15;

// Setup variables.
define("context" , "folio_setup");
global $CFG,$db;
require_once ("../../../includes.php");
require_once '../config.php';
require_once '../lib.php';


ini_set('max_execution_time', 3000);

if (!$rs = $db->Execute("SELECT version();")) {
    trigger_error('Error accessing MySQL database when trying to run SELECT version()', E_USER_ERROR);
}

error_reporting (E_ALL);

// Ensure we're on the correct version of PHP
echo 'Testing to make sure you are running PHP5.  If you get an error after this message, ' .
    'please upgrade to php5<br/>';
// This will cause PHP 4 to die, as it doesn't support the public statement.
class folio_test {
    public $test_var;
}
echo 'Passed PHP5 test<br/><br/>';

// Start upgrading.
if ( folio_getVersion() < $folio_dbversion ) {
    echo ( 'Your DB scheme does not match the most current version...  Beginning upgrade.');
    // I know the below is moronic from a style perspective, but it works
    //        and runs so rarely its not worth redoing in a proper manner.
    switch ( folio_getVersion() ) {
            case -1:
               gotoVersion1();
               // no break, need to continue updating.
            case 1:
               gotoVersion2();
            case 2:
               gotoVersion3();
            case 3:
               gotoVersion4();
            case 4:
               gotoVersion5();
            case 5:
               gotoVersion6();
            case 6:
               gotoVersion7();
            case 7:
               gotoVersion8();
            case 8:
               gotoVersion9();
            case 9:
               gotoVersion10();
            case 10:
               gotoVersion11();
            case 11:
               gotoVersion12();
            case 12:
               gotoVersion13();
            case 13:
               gotoVersion14();
            case 14:
               gotoVersion15();
               // no break, need to continue updating.
    }
    echo ( '<br/>Update finished<br/>' );
} else {

    echo ( 'Your DB scheme is up to date.  Exiting function.');
}

/**
* Get the current version, or -1 if the table isn't present
* @return Integer
**/
function folio_getVersion() {
    global $CFG;

    try {
        // Find.
        $versions = recordset_to_array(
            get_recordset_sql("SELECT version as v, version FROM " . $CFG->prefix . "folio_version")
            );

        if ( $versions ) {
            $i = -1;
            foreach ($versions as $version) {
                if ($i < $version->version) {
                    $i = $version->version;
                }
            }
            return $version->version;
        } else {
            // Table not found.  Folios probably aren't installed yet.
            return -1;
        }

    } catch ( Exception $e ) {
        // Version table probably not found yet.
        return -1;
    }
}


/**
* Update to version 15, move old versions of pages into an archive table.
**/
function gotoVersion15() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

    
    // Create archive table.
        execute_sql(
"CREATE TABLE `{$db}`.`{$prefix}folio_page_archive` (
  `page_ident` int(11) NOT NULL default '0',
  `user_ident` int(11) NOT NULL default '0',
  `created` int(11) NOT NULL default '0',
  `creator_ident` int(11) default NULL,
  `title` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `newest` tinyint(1) NOT NULL default '0',
  `parentpage_ident` int(11) NOT NULL default '-1',
  `format_ident` int(11) NOT NULL default '0',
  `body` text collate utf8_unicode_ci NOT NULL,
  `security_ident` int(11) NOT NULL default '-1',
  `attachments` text collate utf8_unicode_ci NOT NULL,
  `help` text collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`page_ident`,`created`),
  KEY `Index_user` (`user_ident`),
  KEY `Index_title` (`title`),
  KEY `Index_newest` (`newest`),
  KEY `Index_parent` (`parentpage_ident`)
            );"
            );

    // Copy all records over
        execute_sql( "INSERT INTO `{$db}`.`{$prefix}folio_page_archive` " .
        "SELECT * FROM `{$db}`.`{$prefix}folio_page`" );

    // Delete all old records
        execute_sql( 
        "DELETE FROM `{$db}`.`{$prefix}folio_page` WHERE newest <> 1" );
    
    
    $version = new StdClass;
    $version->version = 15;
    insert_record("folio_version",$version);
}


/**
* Update to version 14, creating logging table.
**/
function gotoVersion14() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

    execute_sql(
            "CREATE TABLE  `{$db}`.`{$prefix}folio_history_pagelog` (
              `pagelog_ident` int(11) NOT NULL auto_increment,
			  `user_ident` int(11) NOT NULL default '0',
              `page_ident` int(11) NOT NULL default '0',
			  `owner_ident` int(11) NOT NULL default '0',
              `action` varchar(255) NOT NULL default '',
			  `logged` int(10) unsigned NOT NULL default '0',
              PRIMARY KEY  (`pagelog_ident`)
            );"
            );


    $version = new StdClass;
    $version->version = 14;
    insert_record("folio_version",$version);
}

/**
* Update to version 13, standardizing the format_ident = 4 and adding attachments field.
**/
function gotoVersion13() {
    global $CFG;
    global $FOLIO_CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

    // Add attachments field
    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_page
    ADD COLUMN attachments TEXT NOT NULL;
 ");

    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_page
    ADD COLUMN help TEXT NOT NULL;
 ");

 
    // Add version field
    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_decoration
    ADD COLUMN newest tinyint(1) NOT NULL DEFAULT 0;
 ");

    // Update all existing to 1
    execute_sql("
    update `{$prefix}folio_decoration` SET newest = 1;
 ");

    // Add version field
    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_artifact
    ADD COLUMN newest tinyint(1) NOT NULL DEFAULT 0;
 ");

    // Update all existing to 1
    execute_sql("
    update `{$prefix}folio_artifact` SET newest = 1;
 ");

    // Update everything to format_ident = 3
    //    This will cause everything to use the same data format, and depend on the
    //    interface to do either a normal wiki style or a richer portfolio style.

    execute_sql("
    update `{$prefix}folio_page` SET attachments = body WHERE format_ident = 2
 ");

    execute_sql("
    update `{$prefix}folio_page` SET body = '' WHERE format_ident = 2
 ");

    execute_sql("
    update `{$prefix}folio_page` SET format_ident = 3
 ");

    $version = new StdClass;
    $version->version = 13;
    insert_record("folio_version",$version);
}

/**
* Update to version 12, depreciated.
**/
function gotoVersion12() {
    $version = new StdClass;
    $version->version = 12;
    insert_record("folio_version",$version);
}

/**
* Update to version 11, depreciated, kept for backwards compatability.
**/
function gotoVersion11() {
    $version = new StdClass;
    $version->version = 11;
    insert_record("folio_version",$version);
}
/**
* Update to version 10, creating artifact table.
**/
function gotoVersion10() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

    execute_sql(
            "CREATE TABLE  `{$db}`.`{$prefix}folio_artifact` (
              `artifact_ident` int(11) NOT NULL auto_increment,
              `page_ident` int(11) NOT NULL default '0',
              `page_owner` int(11) NOT NULL default '0',
              `item_type` varchar(100) NOT NULL default '',
              `item_ident` int(11) NOT NULL default '0',
              `item_owner` int(11) NOT NULL default '0',
              `title` varchar(255) NOT NULL default '',
              `text` text NOT NULL,
              `reflection` text NOT NULL,
              PRIMARY KEY  (`artifact_ident`)
            );"
            );

    execute_sql(
            "CREATE TABLE  `{$db}`.`{$prefix}folio_decoration` (
              `decoration_ident` int(11) NOT NULL auto_increment,
              `page_ident` int(11) NOT NULL default '0',
              `type` varchar(255) NOT NULL default '',
              `text` text NOT NULL,
              PRIMARY KEY  (`decoration_ident`)
            );"
            );

    execute_sql(
            "CREATE TABLE  `{$db}`.`{$prefix}folio_page_ident_generator` (
              `page_ident` int(11) NOT NULL auto_increment,
              PRIMARY KEY  (`page_ident`)
            );"
            );

    $version = new StdClass;
    $version->version = 10;
    insert_record("folio_version",$version);
}


/**
* Update to version 9, reset wiki permissions in the global RSS table.
**/
function gotoVersion9() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;


    echo '<br/>Updating access levels for rss table.  This may take several minutes.  If it times out, please remove ' .
        ' older entries from your elgg_folio_rss table, or comment out completed steps from mod/folio/setupdb.php<br/>';

/*
    echo 'Step 1<br/>';

    // Pull records from DB & run the api to auto-load them.
    $records = recordset_to_array(get_recordset_sql(
        "SELECT * FROM " . $CFG->prefix . "folio_page e WHERE newest = 1"));

    if ( $records) {
        foreach ( $records as $record) {

        // Assume only a single access level per page.
            $security = get_record_select('folio_page_security','security_ident = ' . $record->page_ident );
            $security = $security->accesslevel;
            if ( $security == 'PRIVATE' ) {
                $user = get_record_select('users','ident = ' . $record->user_ident );
                if ( $user->user_type == 'community' ) {
                    $security = 'community' . $record->user_ident;
                } else {
                    $security = 'user' . $record->user_ident;
                }
            }

            // Set all permissions to the permission of the latest wiki entry.
            execute_sql( "UPDATE {$db}.{$prefix}folio_rss SET access = '{$security}'
                    WHERE type_ident = {$record->page_ident} AND type='page'");



        }
    }
*/
echo 'Step 2<br/>';

    // Re-do the load again of rss entries so that dates are correctly pulled.
    execute_sql( "DELETE FROM {$db}.{$prefix}folio_rss where type IN ( 'weblog', 'weblog_comment', 'file')");

    // Pull records from DB & run the api to auto-load them.

    // BLOG
    $records = recordset_to_array(get_recordset_sql(
        "SELECT * FROM " . $CFG->prefix . "weblog_posts e"));
    if ( $records) {
        foreach ( $records as $record) {
            folio_rss_weblog_post( 'weblog_post', 'publish', $record );
        }
    }

    echo 'Step 3<br/>';

    // COMMENTS
    $records = recordset_to_array(get_recordset_sql(
        "SELECT * FROM " . $CFG->prefix . "weblog_comments e"));
    if ( $records) {
        foreach ( $records as $record) {
            folio_rss_weblog_comment_post( 'weblog_comment', 'publish', $record );
        }
    }

    echo 'Step 4<br/>';

    // FILES
    $records = recordset_to_array(get_recordset_sql(
        "SELECT * FROM " . $CFG->prefix . "files e"));

    if ( $records) {
        foreach ( $records as $record) {
            folio_rss_file_post( 'file', 'publish', $record );
        }
    }
    $version = new StdClass;
    $version->version = 9;
    insert_record("folio_version",$version);
}


/**
* Update to version 8, depreciated
**/
function gotoVersion8() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

// depreciated

    $version = new StdClass;
    $version->version = 8;
    insert_record("folio_version",$version);
}


/**
* Update to version 7, depreciated
**/
function gotoVersion7() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

// depreciated

    $version = new StdClass;
    $version->version = 7;
    insert_record("folio_version",$version);
}


/**
* Update to version 6, created logging facilities for the "inbox"
**/
function gotoVersion6() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

    execute_sql(
            "
CREATE TABLE {$db}.{$prefix}folio_history_logon (
  `user_ident` int(10) unsigned NOT NULL default '0',
  `created` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`user_ident`, `created`))"
    );

    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_history_logon
    ADD INDEX Index_owner(user_ident);
 ");

    $version = new StdClass;
    $version->version = 6;
    insert_record("folio_version",$version);
}


/**
* Update to version 5, added indexing & a new column for RSS.
**/
function gotoVersion5() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_rss
    ADD COLUMN access VARCHAR(255) NOT NULL DEFAULT 'PUBLIC' AFTER owner_username;
 ");

    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_rss
    ADD INDEX Index_user(user_ident),
    ADD INDEX Index_owner(owner_ident);
 ");
    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_page
    ADD INDEX Index_user(user_ident),
    ADD INDEX Index_title(title),
    ADD INDEX Index_newest(newest),
    ADD INDEX Index_parent(parentpage_ident);
");

    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_comment
    ADD INDEX Index_type(item_type),
    ADD INDEX Index_ident(item_type);
 ");

    execute_sql("
ALTER TABLE {$db}.{$prefix}folio_page_security
    ADD INDEX Index_security(security_ident);
 ");

    $version = new StdClass;
    $version->version = 5;
    insert_record("folio_version",$version);
}

/**
* Update to version 4, updated comment table to differentiate between owner & poster
**/
function gotoVersion4() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

    execute_sql(
            "
ALTER TABLE {$db}.{$prefix}folio_comment CHANGE COLUMN `owner` `creator_ident` INTEGER NOT NULL DEFAULT 0,
 CHANGE COLUMN `postedname` `creator_name` VARCHAR(254) NOT NULL DEFAULT '',
 ADD COLUMN `item_owner_ident` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `posted`,
 ADD COLUMN `item_title` VARCHAR(254) NOT NULL DEFAULT '' AFTER `item_ident`,
 ADD COLUMN `item_owner_username` VARCHAR(254) NOT NULL DEFAULT '' AFTER `item_title`,
 ADD COLUMN `item_owner_name` VARCHAR(254) NOT NULL DEFAULT '' AFTER `item_owner_username`,
 ADD COLUMN `creator_username` VARCHAR(254) NOT NULL DEFAULT '' AFTER `item_owner_name`;
 "
    );

    execute_sql(
            "
ALTER TABLE {$db}.{$prefix}folio_rss ADD COLUMN `owner_username` VARCHAR(255) NOT NULL DEFAULT '' AFTER `created`;
 "
    );



    $version = new StdClass;
    $version->version = 4;
    insert_record("folio_version",$version);
}


/**
* Update to version 3, added rss table
**/
function gotoVersion3() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

    execute_sql(
            "
CREATE TABLE {$db}.{$prefix}folio_rss (
  `type_ident` int(10) unsigned NOT NULL default '0',
  `type` varchar(45) NOT NULL default '',
  `user_ident` int(10) unsigned NOT NULL default '0',
  `user_username` varchar(45) NOT NULL default '',
  `user_name` varchar(255) NOT NULL default '',
  `owner_ident` int(10) unsigned NOT NULL default '0',
  `title` text NOT NULL,
  `body` text NOT NULL,
  `link` varchar(255) NOT NULL default '',
  `created` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`type_ident`,`type`,`created`))"
    );

    $version = new StdClass;
    $version->version = 3;
    insert_record("folio_version",$version);
}


/**
* Update to version 2, added a column to a table.
**/
function gotoVersion2() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

    execute_sql(
            "ALTER TABLE {$db}.{$prefix}folio_page ADD creator_ident Integer AFTER created;"
            );

    execute_sql(
            "UPDATE {$db}.{$prefix}folio_page SET creator_ident = user_ident;"
            );



    $version = new StdClass;
    $version->version = 2;
    insert_record("folio_version",$version);
}



/**
* Update to version 1 -- first db schema
**/
function gotoVersion1() {
    global $CFG;
    $prefix = $CFG->prefix;
    $db = $CFG->dbname;

/* ---------------------------------------------------------------
Page
Currently not using formatpage_ident column.  Built in now to support templating.
--------------------------------------------------------------- */
    execute_sql(
            "CREATE TABLE  `{$db}`.`{$prefix}folio_page` (
              `page_ident` int(11) NOT NULL default '0',
              `user_ident` int(11) NOT NULL default '0',
              `created` int(11) NOT NULL default '0',
              `title` varchar(255) NOT NULL default '',
              `newest` tinyint(1) NOT NULL default '0',
              `parentpage_ident` int(11) NOT NULL default '-1',
              `format_ident` int(11) NOT NULL default '0',
              `body` text NOT NULL,
              `security_ident` int(11) NOT NULL default '-1',
              PRIMARY KEY  (`page_ident`,`created`)
            );"
            );

/* ---------------------------------------------------------------
 Page Security

There can be multiple security records for a single page.  They differ
only in that they have multiple user_ids.  They should all have the same
security levels.  This lets us keep track of the different involved users.

Currently 3 levels are implemented, public, moderated, and private.  Public
is wide open, everyone can read/write.  Moderated is public readable, but
only setable by owners.  Private is readable and setable only by owners.
Owners are defined as people who have a security record with their id.

The interface doesn't currently handle inheriting.  Will re-implement again
at a later date.
         --------------------------------------------------------------- */
    execute_sql(
            "CREATE TABLE  `{$db}`.`{$prefix}folio_page_security` (
              `security_ident` int(11) NOT NULL default '0',
              `user_ident` int(11) NOT NULL default '0',
              `accesslevel` varchar(100) NOT NULL default 'member',
              PRIMARY KEY  (`user_ident`,`security_ident`,`accesslevel`)
            );"
            );

/* ---------------------------------------------------------------
Tree

This contains nodes for navigating between objects.

Probably needs some more indexes (along with the rest of the folio
tables).

Not currently being used.  Will implement for the presentation module, and possibly
the ability to import/link to other people's pages.
--------------------------------------------------------------- */
    execute_sql(
            "CREATE TABLE  `{$db}`.`{$prefix}folio_tree` (
          `node_ident` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
          `alink_type` VARCHAR(45) NOT NULL DEFAULT '',
          `alink_ident` INTEGER UNSIGNED NOT NULL DEFAULT 0,
          `blink_type` VARCHAR(45) NOT NULL DEFAULT '',
          `blink_ident` INTEGER UNSIGNED NOT NULL DEFAULT 0,
          `blink_caption` VARCHAR(45) NOT NULL DEFAULT '',
          PRIMARY KEY(`node_ident`),
          INDEX `a_link`(`alink_ident`),
          INDEX `b_link`(`blink_ident`)
            );"
            );

/* ---------------------------------------------------------------
Folio Comments.
Comments on different items inside of the add-in.
--------------------------------------------------------------- */

    execute_sql(
            "CREATE TABLE  `{$db}`.`{$prefix}folio_comment` (
              `activity_ident` int(11) NOT NULL auto_increment,
              `item_type` varchar(100) NOT NULL default '',
              `item_ident` int(11) NOT NULL default '0',
              `owner` int(11) NOT NULL default '0',
              `postedname` varchar(100) NOT NULL default '',
              `body` text NOT NULL,
              `access` varchar(100) NOT NULL default '',
              `posted` int(11) NOT NULL default '0',
              PRIMARY KEY  (`activity_ident`)
            );"
            );

/* ---------------------------------------------------------------
Tracks what version of the db we're working with.
--------------------------------------------------------------- */

    execute_sql(
            "CREATE TABLE  `{$db}`.`{$prefix}folio_version` (
              `version` int(10) unsigned NOT NULL auto_increment,
              PRIMARY KEY  (`version`)
            );"
    );

// Update version to schema 1
    $version = new StdClass;
    $version->version = 1;
    insert_record("folio_version",$version);

}
?>
